package Frontend

import (
	"encoding/json"
	"fmt"
	"github.com/aliyun/alibaba-cloud-sdk-go/services/dysmsapi"
	"github.com/tealeg/xlsx"
	"io/ioutil"
	"mime"
	"net/http"
	"os"
	"path/filepath"
	"strconv"
	"time"
)

// 报名审核接口
// 获取待审核人员信息 get参数 当前页面 一页显示数量
// 							page page_size
//func Admin_get_shenhe_class(w http.ResponseWriter, r *http.Request) {
//	w.Header().Set("Access-Control-Allow-Origin", "*")
//	// 传给前台的数据
//	type user_data struct {
//		Id                  int    `db:"id"json:"id"`
//		Name                string `db:"name"json:"name"`
//		Iphone              string `db:"iphone"json:"iphone"`
//		Idcard              string `db:"idcard"json:"idcard"`
//		Address             string `db:"address"json:"address"`
//		Party               string `db:"party"json:"party"`
//		Degree              string `db:"degree"json:"degree"`
//		Urgent_name         string `db:"urgent_name"json:"urgent_name"`
//		Urgent_relationship string `db:"urgent_relationship"json:"urgent_relationship"`
//		Urgent_iphone       string `db:"urgent_iphone"json:"urgent_iphone"`
//		Disease             string `db:"disease"json:"disease"`
//		Sex                 string `db:"sex"json:"sex"`
//		Age                 string `db:"age"json:"age"`
//		Register_record_id  int    `db:"register_record_id"json:"register_record_id"`
//		Calss_name          string `db:"calss_name"json:"calss_name"`
//		Calss_id            int    `db:"calss_id"json:"calss_id"`
//		Course_name         string `db:"course_name"json:"course_name"`
//		Course_id           int    `db:"course_id"json:"course_id"`
//		Profession_name     string `db:"profession_name"json:"profession_name"`
//		Profession_id       int    `db:"profession_id"json:"profession_id"`
//		Date                string `db:"date"json:"date"`
//	}
//	data := r.URL.Query()
//	page_str := data.Get("page")
//	page_size_str := data.Get("page_size")
//	page, _ := strconv.Atoi(page_str)
//	page_size, _ := strconv.Atoi(page_size_str)
//	y1 := (page - 1) * page_size
//	y2 := page * page_size
//
//	// 先查询报名记录里面待审核的信息
//	sqlStr := `select * from register_record where statu=? ORDER BY date DESC,wx_user_id DESC LIMIT ?,?;`
//	var my_register_record []mysql_register_record
//	err := db.Select(&my_register_record, sqlStr, "审核中", y1, y2)
//	if err != nil {
//		fmt.Println("query failed, err:%v\n", err)
//		return
//	}
//	//fmt.Println("users:%#v\n", my_register_record)
//	num_register_record := 0
//	sqlStr = "select COUNT(*) from register_record where statu=?;"
//	err = db.QueryRow(sqlStr,"审核中").Scan(&num_register_record)
//	if err != nil {
//		fmt.Printf("scan failed, err:%v\n", err)
//		return
//	}
//	// 查询用户所有数据
//	sqlStr = `select * from wx_user;`
//	var my_wx_user []mysql_wx_user
//	err = db.Select(&my_wx_user, sqlStr)
//	if err != nil {
//		fmt.Println("query failed, err:%v\n", err)
//		return
//	}
//	//fmt.Println("my_wx_user:%#v\n", my_wx_user)
//
//	// 查询班级信息
//	sqlStr = `select * from class;`
//	var my_class []mysql_class
//	err = db.Select(&my_class, sqlStr)
//	if err != nil {
//		fmt.Println("query failed, err:%v\n", err)
//		return
//	}
//	//fmt.Println("my_class:%#v\n", my_class)
//
//	// 查询课程信息
//	sqlStr = `select * from course;`
//	var my_course []mysql_course
//	err = db.Select(&my_course, sqlStr)
//	if err != nil {
//		fmt.Println("query failed, err:%v\n", err)
//		return
//	}
//	//fmt.Println("my_course:%#v\n", my_course)
//
//	// 查询专业信息
//	sqlStr = `select * from profession;`
//	var my_profession []mysql_profession
//	err = db.Select(&my_profession, sqlStr)
//	if err != nil {
//		fmt.Println("query failed, err:%v\n", err)
//		return
//	}
//	//fmt.Println("my_profession:%#v\n", my_profession)
//
//	// 返回给前台的数据结构体
//	type TableData_class struct {
//		Num_register_record int `json:"num_register_record"`
//		TableData []user_data `json:"tableData"`
//	}
//	// 用户数组
//	var tableData_list TableData_class
//
//	// 循环带审核记录
//	for i:=0;i<len(my_register_record);i++ {
//		var user user_data
//		user_id:=my_register_record[i].Wx_user_id
//		class_id:=my_register_record[i].Class_id
//		user.Date=my_register_record[i].Date[:10]
//		user.Register_record_id=my_register_record[i].Id
//		// 循环用户
//		for x:=0;x<len(my_wx_user);x++ {
//			// 如果查找到一样的用户，克隆他的资料
//			if my_wx_user[x].Id==user_id{
//				user.Id=my_wx_user[x].Id
//				user.Name=my_wx_user[x].Name
//				user.Iphone=my_wx_user[x].Iphone
//				user.Idcard=my_wx_user[x].Idcard
//				user.Address=my_wx_user[x].Address
//				user.Party=my_wx_user[x].Party
//				user.Degree=my_wx_user[x].Degree
//				user.Urgent_name=my_wx_user[x].Urgent_name
//				user.Urgent_relationship=my_wx_user[x].Urgent_relationship
//				user.Urgent_iphone=my_wx_user[x].Urgent_iphone
//				user.Disease=my_wx_user[x].Disease
//				user.Sex=my_wx_user[x].Sex
//				user.Age=my_wx_user[x].Age
//			}
//		}
//
//		//循环班级课程专业资料
//		// 循环班级
//		for x:=0;x<len(my_class);x++ {
//			// 如果相等，我录入相等的
//			if my_class[x].Id==class_id {
//				user.Calss_id=my_class[x].Id
//				user.Calss_name=my_class[x].Name
//				// 继续查他的课程
//				for y:=0;y<len(my_course);y++ {
//					if my_course[y].Id==my_class[x].Course_id {
//						user.Course_id=my_course[y].Id
//						user.Course_name=my_course[y].Name
//						//继续查他的专业
//						for z:=0;z<len(my_profession);z++ {
//							if my_profession[z].Id==my_course[y].Profession_id {
//								user.Profession_id=my_profession[z].Id
//								user.Profession_name=my_profession[z].Name
//							}
//						}
//					}
//				}
//			}
//		}
//		tableData_list.TableData = append(tableData_list.TableData, user)
//	}
//	tableData_list.Num_register_record=num_register_record
//	data_str, err := json.Marshal(tableData_list)
//	if err != nil {
//		fmt.Println("json marshal failed")
//		return
//	}
//	//fmt.Printf("data_str:%s\n", data_str)
//	w.Write(data_str) //给客户端返回数据。
//}
// page=当前页码 page_size=一页显示数量 user_name=姓名 user_iphone=手机号 user_statu=审核中 user_idcard=身份证号码
func Admin_get_shenhe_class(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	// 传给前台的数据
	type user_data struct {
		Id                  int       `db:"id"json:"id"`
		Name                string    `db:"name"json:"name"`
		Iphone              string    `db:"iphone"json:"iphone"`
		Idcard              string    `db:"idcard"json:"idcard"`
		Address             string    `db:"address"json:"address"`
		Party               string    `db:"party"json:"party"`
		Degree              string    `db:"degree"json:"degree"`
		Urgent_name         string    `db:"urgent_name"json:"urgent_name"`
		Urgent_relationship string    `db:"urgent_relationship"json:"urgent_relationship"`
		Urgent_iphone       string    `db:"urgent_iphone"json:"urgent_iphone"`
		Disease             string    `db:"disease"json:"disease"`
		Sex                 string    `db:"sex"json:"sex"`
		Age                 string    `db:"age"json:"age"`
		Register_record_id  int       `db:"register_record_id"json:"register_record_id"`
		Class_name          string    `db:"class_name"json:"class_name"`
		Class_id            int       `db:"class_id"json:"class_id"`
		Class_money         float32   `db:"class_money"json:"class_money"`
		Course_name         string    `db:"course_name"json:"course_name"`
		Course_id           int       `db:"course_id"json:"course_id"`
		Profession_name     string    `db:"profession_name"json:"profession_name"`
		Profession_id       int       `db:"profession_id"json:"profession_id"`
		Date                time.Time `db:"date"json:"date"`
		Date_str            string    `db:"date_str"json:"date_str"`
	}
	data := r.URL.Query()
	page_str := data.Get("page")
	page_size_str := data.Get("page_size")
	user_name := data.Get("user_name")
	user_name = user_name + `%`
	user_iphone := data.Get("user_iphone")
	user_iphone = user_iphone + `%`
	user_statu := data.Get("user_statu")
	user_idcard := data.Get("user_idcard")
	user_idcard = user_idcard + `%`

	page, _ := strconv.Atoi(page_str)
	page_size, _ := strconv.Atoi(page_size_str)
	y1 := (page - 1) * page_size
	y2 := page_size
	fmt.Println("y1=", y1)
	fmt.Println("y2=", y2)
	// 获取数量
	num_register_record := 0
	sqlStr := `select COUNT(*) from register_record where statu=?;`
	err := db.QueryRow(sqlStr, user_statu).Scan(&num_register_record)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Println("user_statu=", user_statu)
	fmt.Println("查询数量=", num_register_record)
	// 返回给前台的数据结构体
	type TableData_class struct {
		Num_register_record int         `json:"num_register_record"`
		TableData           []user_data `json:"tableData"`
	}
	// 用户数组
	var tableData_list TableData_class
	tableData_list.Num_register_record = num_register_record

	sqlStr = `select b.id AS id,b.name AS name,b.iphone AS iphone,b.idcard AS idcard,b.address AS address,b.party AS party,b.degree AS degree,b.urgent_name AS urgent_name,b.urgent_relationship AS urgent_relationship,b.urgent_iphone AS urgent_iphone,b.disease AS disease,b.sex AS sex,b.age AS age,a.id AS register_record_id,c.name AS class_name,c.id AS class_id,cc.id AS course_id,p.name AS profession_name,cc.name AS course_name,p.id AS profession_id,a.date AS date,c.money AS class_money from register_record a INNER Join wx_user b ON a.wx_user_id=b.id INNER Join class c ON a.class_id=c.id INNER Join course cc ON c.course_id=cc.id INNER Join profession p ON cc.profession_id=p.id WHERE a.statu=? AND b.name LIKE ? AND b.iphone LIKE ? AND idcard LIKE ? ORDER BY a.date DESC,a.wx_user_id DESC LIMIT ?,?;`

	err = db.Select(&tableData_list.TableData, sqlStr, user_statu, user_name, user_iphone, user_idcard, y1, y2)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	for i := 0; i < len(tableData_list.TableData); i++ {
		tableData_list.TableData[i].Date_str = tableData_list.TableData[i].Date.Format("2006-01-02 15:04:05")
	}
	data_str, err := json.Marshal(tableData_list)
	if err != nil {
		fmt.Println("json marshal failed")
		return
	}
	//fmt.Printf("date-1=", tableData_list.TableData[0].Date)
	w.Write(data_str) //给客户端返回数据。
}

// 已通过 写入数据库并且累加
func Admin_up_register_record_statuok(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")

	register_arr_str := r.PostFormValue("register_arr")
	fmt.Println("register_arr_str=", register_arr_str)
	type register_class struct {
		Reg_id   int `db:"reg_id"json:"reg_id"`
		Class_id int `db:"class_id"json:"class_id"`
		Iphone string `db:"iphone"json:"iphone"`
		Class_name string `db:"class_name"json:"class_name"`
	}
	var arr []register_class
	err := json.Unmarshal([]byte(register_arr_str), &arr)
	if err != nil {
		fmt.Println("json unmarshal failed!=", err)
		return
	}
	//fmt.Println(arr[0].Reg_id)

	for i := 0; i < len(arr); i++ {
		sqlStr := "update register_record set statu=? where id = ?"
		// 写入审核通过信息
		ret, err := db.Exec(sqlStr, "已通过", arr[i].Reg_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
		fmt.Printf("update success, affected rows:%d\n", n)

		//sqlStr = "update class set persons_curr=persons_curr+1 where id = ?"
		//ret, err = db.Exec(sqlStr, arr[i].Class_id)
		//if err != nil {
		//	fmt.Printf("update failed, err:%v\n", err)
		//	return
		//}
		//n, err = ret.RowsAffected() // 操作影响的行数
		//if err != nil {
		//	fmt.Printf("get RowsAffected failed, err:%v\n", err)
		//	return
		//}
		successful_registration("SMS_211481182",arr[i].Iphone,arr[i].Class_name)
	}
}

// 未通过 写入数据库并且减少
func Admin_up_register_record_statuno(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")

	register_arr_str := r.PostFormValue("register_arr")
	fmt.Println("register_arr_str=", register_arr_str)
	type register_class struct {
		Reg_id   int `db:"reg_id"json:"reg_id"`
		Class_id int `db:"class_id"json:"class_id"`
		Iphone string `db:"iphone"json:"iphone"`
		Class_name string `db:"class_name"json:"class_name"`
	}
	var arr []register_class
	err := json.Unmarshal([]byte(register_arr_str), &arr)
	if err != nil {
		fmt.Println("json unmarshal failed!=", err)
		return
	}
	//fmt.Println(arr[0].Reg_id)

	for i := 0; i < len(arr); i++ {
		sqlStr := "update register_record set statu=? where id = ?"
		// 写入审核通过信息
		ret, err := db.Exec(sqlStr, "未通过", arr[i].Reg_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
		fmt.Printf("update success, affected rows:%d\n", n)

		sqlStr = "update class set persons_curr=persons_curr-1 where id = ?"
		ret, err = db.Exec(sqlStr, arr[i].Class_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err = ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
		successful_registration("SMS_211496124",arr[i].Iphone,arr[i].Class_name)
	}
}

// 未通过 写入数据库 但是不减少
func Admin_up_register_record_persons_curr_statuno(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")

	register_arr_str := r.PostFormValue("register_arr")
	fmt.Println("register_arr_str=", register_arr_str)
	type register_class struct {
		Reg_id   int `db:"reg_id"json:"reg_id"`
		Class_id int `db:"class_id"json:"class_id"`
		Iphone string `db:"iphone"json:"iphone"`
		Class_name string `db:"class_name"json:"class_name"`
	}
	var arr []register_class
	err := json.Unmarshal([]byte(register_arr_str), &arr)
	if err != nil {
		fmt.Println("json unmarshal failed!=", err)
		return
	}
	//fmt.Println(arr[0].Reg_id)

	for i := 0; i < len(arr); i++ {
		sqlStr := "update register_record set statu=? where id = ?"
		// 写入审核通过信息
		ret, err := db.Exec(sqlStr, "未通过", arr[i].Reg_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
		sqlStr = "update class set persons_curr=persons_curr-1 where id = ?"
		ret, err = db.Exec(sqlStr, arr[i].Class_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err = ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
		fmt.Printf("update success, affected rows:%d\n", n)
		successful_registration("SMS_211496124",arr[i].Iphone,arr[i].Class_name)
	}
}

// 导出指定审核页面的数据 传入审核状态 user_statu 审核中，已通过，未通过
// date_min 最小时间 date_max 最大时间 get方式
func Xls_derived(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	data := r.URL.Query()
	user_statu := data.Get("user_statu")
	date_min := data.Get("date_min")
	date_max := data.Get("date_max")

	type mysql_users struct {
		Userid string `db:"userid"json:"userid"`
		Name   string `db:"name"json:"name"`
		Iphone string `db:"iphone"json:"iphone"`
	}

	var users []mysql_users
	if date_min == "" || date_max == "" {
		fmt.Println("导出全部")
		// 传入时间为空 导出全部
		sqlStr := `SELECT DISTINCT(userid),name,iphone FROM wx_user w LEFT JOIN register_record r ON w.id=r.wx_user_id WHERE r.statu=?;`
		err := db.Select(&users, sqlStr, user_statu)
		if err != nil {
			fmt.Printf("query failed, err:%v\n", err)
			return
		}
	} else {
		fmt.Println("按照时间到处")
		sqlStr := `SELECT DISTINCT(userid),name,iphone FROM wx_user w LEFT JOIN register_record r ON w.id=r.wx_user_id WHERE r.statu=? AND r.date>=? AND r.date<=?;`
		err := db.Select(&users, sqlStr, user_statu, date_min, date_max)
		if err != nil {
			fmt.Printf("query failed, err:%v\n", err)
			return
		}
	}
	//JSON序列化：结构体-->JSON格式的字符串
	//data_str, err := json.Marshal(users)
	//if err != nil {
	//	fmt.Println("json marshal failed")
	//	return
	//}
	//fmt.Println(data_str)
	//w.Write(data_str) //给客户端返回数据。
	timeUnix := time.Now().Unix() //单位秒
	file_name := `bm` + strconv.FormatInt(timeUnix, 10) + `.xlsx`
	outFile := `/www/wwwroot/www.lmtqlndx.com/assign/xls/` + file_name
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("student_list")
	if err != nil {
		fmt.Printf(err.Error())
	}
	stus := users
	row := sheet.AddRow()
	lie1 := row.AddCell()
	lie1.Value = "姓名"
	lie2 := row.AddCell()
	lie2.Value = "手机号"
	//add data
	for _, stu := range stus {
		row := sheet.AddRow()
		name := row.AddCell()
		name.Value = stu.Name

		Iphone := row.AddCell()
		Iphone.Value = stu.Iphone
	}
	err = file.Save(outFile)
	if err != nil {
		fmt.Printf(err.Error())
	}
	fmt.Println("\n\nexport success")
	jsonk := `{"isok":"ok","file_path":` + `"/assign/xls/` + file_name + `"` + `}`
	fmt.Println("返回前台数据为：", jsonk)
	w.Write([]byte(jsonk)) //给客户端返回数据。
}

// 导出表格 用于备案信息
func Xls_derived_beian(w http.ResponseWriter, r *http.Request) {
	type user_data struct {
		Id                  int       `db:"id"json:"id"`
		Name                string    `db:"name"json:"name"`
		Iphone              string    `db:"iphone"json:"iphone"`
		Idcard              string    `db:"idcard"json:"idcard"`
		Address             string    `db:"address"json:"address"`
		Party               string    `db:"party"json:"party"`
		Degree              string    `db:"degree"json:"degree"`
		Urgent_name         string    `db:"urgent_name"json:"urgent_name"`
		Urgent_relationship string    `db:"urgent_relationship"json:"urgent_relationship"`
		Urgent_iphone       string    `db:"urgent_iphone"json:"urgent_iphone"`
		Disease             string    `db:"disease"json:"disease"`
		Sex                 string    `db:"sex"json:"sex"`
		Age                 string    `db:"age"json:"age"`
		Class_name          string    `db:"class_name"json:"class_name"`
		Class_money         float32   `db:"class_money"json:"class_money"`
		Course_name         string    `db:"course_name"json:"course_name"`
		Profession_name     string    `db:"profession_name"json:"profession_name"`
		Date                time.Time `db:"date"json:"date"`
		Date_str            string    `db:"date_str"json:"date_str"`
	}
	data := r.URL.Query()
	user_statu := data.Get("user_statu")
	date_min := data.Get("date_min")
	date_max := data.Get("date_max")
	fmt.Println("user_statu=", user_statu)
	fmt.Println("date_min=", date_min)
	fmt.Println("date_max=", date_max)
	var users []user_data
	var sqlStr string
	if date_min == "" || date_max == "" {
		sqlStr = `select b.id AS id,b.name AS name,b.iphone AS iphone,b.idcard AS idcard,b.address AS address,b.party AS party,b.degree AS degree,b.urgent_name AS urgent_name,b.urgent_relationship AS urgent_relationship,b.urgent_iphone AS urgent_iphone,b.disease AS disease,b.sex AS sex,b.age AS age,c.name AS class_name,p.name AS profession_name,cc.name AS course_name,a.date AS date,c.money AS class_money from register_record a INNER Join wx_user b ON a.wx_user_id=b.id INNER Join class c ON a.class_id=c.id INNER Join course cc ON c.course_id=cc.id INNER Join profession p ON cc.profession_id=p.id WHERE a.statu=? ORDER BY a.date DESC,a.wx_user_id DESC;`
		err := db.Select(&users, sqlStr, user_statu)
		if err != nil {
			fmt.Printf("query failed, err:%v\n", err)
			return
		}
	} else {
		sqlStr = `select b.id AS id,b.name AS name,b.iphone AS iphone,b.idcard AS idcard,b.address AS address,b.party AS party,b.degree AS degree,b.urgent_name AS urgent_name,b.urgent_relationship AS urgent_relationship,b.urgent_iphone AS urgent_iphone,b.disease AS disease,b.sex AS sex,b.age AS age,c.name AS class_name,p.name AS profession_name,cc.name AS course_name,a.date AS date,c.money AS class_money from register_record a INNER Join wx_user b ON a.wx_user_id=b.id INNER Join class c ON a.class_id=c.id INNER Join course cc ON c.course_id=cc.id INNER Join profession p ON cc.profession_id=p.id WHERE a.statu=? AND a.date>=? AND a.date<=? ORDER BY a.date DESC,a.wx_user_id DESC;`
		err := db.Select(&users, sqlStr, user_statu, date_min, date_max)
		if err != nil {
			fmt.Printf("query failed, err:%v\n", err)
			return
		}
	}

	for i := 0; i < len(users); i++ {
		users[i].Date_str = users[i].Date.Format("2006-01-02 15:04:05")
	}
	//data_str, err := json.Marshal(users)
	//if err != nil {
	//	fmt.Println("json marshal failed")
	//	return
	//}
	fmt.Printf("date-1=", users[0])
	//w.Write(data_str) //给客户端返回数据。

	//生成xlsx文件
	timeUnix := time.Now().Unix() //单位秒
	file_name := `bm` + strconv.FormatInt(timeUnix, 10) + `.xlsx`
	outFile := `/www/wwwroot/www.lmtqlndx.com/assign/xls/` + file_name
	//outFile := `./` + file_name
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("student_list")
	if err != nil {
		fmt.Printf(err.Error())
	}
	stus := users
	// 制作标题中
	row := sheet.AddRow()
	lie1 := row.AddCell()
	lie1.Value = "姓名"
	lie1 = row.AddCell()
	lie1.Value = "手机号"
	lie1 = row.AddCell()
	lie1.Value = "身份证号码"
	lie1 = row.AddCell()
	lie1.Value = "地址"
	lie1 = row.AddCell()
	lie1.Value = "党派"
	lie1 = row.AddCell()
	lie1.Value = "学历"
	lie1 = row.AddCell()
	lie1.Value = "疾病史"
	lie1 = row.AddCell()
	lie1.Value = "性别"
	lie1 = row.AddCell()
	lie1.Value = "年龄"
	lie1 = row.AddCell()
	lie1.Value = "所报班级"
	lie1 = row.AddCell()
	lie1.Value = "班级学费"
	lie1 = row.AddCell()
	lie1.Value = "所属课程"
	lie1 = row.AddCell()
	lie1.Value = "所属专业"
	lie1 = row.AddCell()
	lie1.Value = "紧急联系人姓名"
	lie1 = row.AddCell()
	lie1.Value = "紧急联系人关系"
	lie1 = row.AddCell()
	lie1.Value = "紧急联系人手机号码"
	lie1 = row.AddCell()
	lie1.Value = "填报时间"

	//add data
	for _, stu := range stus {
		row := sheet.AddRow()
		lie := row.AddCell()
		lie.Value = stu.Name
		lie = row.AddCell()
		lie.Value = stu.Iphone
		lie = row.AddCell()
		lie.Value = stu.Idcard
		lie = row.AddCell()
		lie.Value = stu.Address
		lie = row.AddCell()
		lie.Value = stu.Party
		lie = row.AddCell()
		lie.Value = stu.Degree
		lie = row.AddCell()
		lie.Value = stu.Disease
		lie = row.AddCell()
		lie.Value = stu.Sex
		lie = row.AddCell()
		lie.Value = stu.Age
		lie = row.AddCell()
		lie.Value = stu.Class_name
		lie = row.AddCell()
		lie.Value = strconv.FormatFloat(float64(stu.Class_money), 'f', 2, 64)
		lie = row.AddCell()
		lie.Value = stu.Course_name
		lie = row.AddCell()
		lie.Value = stu.Profession_name
		lie = row.AddCell()
		lie.Value = stu.Urgent_name
		lie = row.AddCell()
		lie.Value = stu.Urgent_relationship
		lie = row.AddCell()
		lie.Value = stu.Iphone
		lie = row.AddCell()
		lie.Value = stu.Date_str
	}
	err = file.Save(outFile)
	if err != nil {
		fmt.Printf(err.Error())
	}
	fmt.Println("\n\nexport success")
	jsonk := `{"isok":"ok","file_path":` + `"/assign/xls/` + file_name + `"` + `}`
	fmt.Println("返回前台数据为：", jsonk)
	w.Write([]byte(jsonk)) //给客户端返回数据。
}

// 删除报名信息，更具课程id
func Delete_class_id(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	data := r.URL.Query()
	register_record_id := data.Get("register_record_id")
	if register_record_id == "" {
		jsonk := `{"isok":"no","msg":""}`
		fmt.Println("返回前台数据为：", jsonk)
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}

	register_record_id_int, err := strconv.Atoi(register_record_id)
	fmt.Println("register_record_id=", register_record_id)
	fmt.Println("register_record_id_int=", register_record_id_int)
	sqlStr := "delete from register_record where id = ?"
	ret, err := db.Exec(sqlStr, register_record_id_int)
	if err != nil {
		fmt.Println("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		jsonk := `{"isok":"no","msg":""}`
		fmt.Println("返回前台数据为：", jsonk)
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	fmt.Println("delete success, affected rows:%d\n", n)
	jsonk := `{"isok":"ok","msg":""}`
	fmt.Println("返回前台数据为：", jsonk)
	w.Write([]byte(jsonk)) //给客户端返回数据。
}

// 清空记录
func Delete_register_record(w http.ResponseWriter, r *http.Request) {
	sqlStr := "delete from register_record"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

var accessKeyId = "LTAI4GEsyxqrMRwKdsuUgyR7"
var accessSecret = "Rju1nU0lNRu3Gyl8ietmQll4RTxtph"
// 发送报名成功短信 只能一个手机号 两个参数iphione何name
// 短信模板号码 templateCode iphone 手机号码
func successful_registration(templateCode string, iphone string,mtname string) bool {
	fmt.Println("调用发送报名成功接口")
	client, err := dysmsapi.NewClientWithAccessKey("cn-hangzhou", accessKeyId, accessSecret)

	request := dysmsapi.CreateSendSmsRequest()
	request.Scheme = "https"
	// 获取手机号
	request.PhoneNumbers = iphone
	request.SignName = "泸州市龙马潭区老年大学"
	request.TemplateCode = templateCode
	request.TemplateParam = `{"mtname":` +`"`+mtname+`"`+ `}`
	response, err := client.SendSms(request)
	if err != nil {
		fmt.Print(err.Error())
		jsonk := `{"isok":"no","msg":"发送失败"}`
		fmt.Println("返回前台数据为：", jsonk)
		return false
	}
	fmt.Printf("response is %#v\n", response)
	jsonk := `{"isok":"ok","msg":"发送完成"}`
	fmt.Println("返回前台数据为：", jsonk)
	return true
}

// 删除报名信息， 并且数据库数量-1
func Delete_class_ids_curr(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")

	register_arr_str := r.PostFormValue("register_arr")
	fmt.Println("register_arr_str=", register_arr_str)
	type register_class struct {
		Reg_id   int `db:"reg_id"json:"reg_id"`
		Class_id int `db:"class_id"json:"class_id"`
		Iphone string `db:"iphone"json:"iphone"`
		Class_name string `db:"class_name"json:"class_name"`
	}
	var arr []register_class
	err := json.Unmarshal([]byte(register_arr_str), &arr)
	if err != nil {
		fmt.Println("json unmarshal failed!=", err)
		return
	}
	//fmt.Println(arr[0].Reg_id)

	for i := 0; i < len(arr); i++ {
		sqlStr := "delete from register_record where id = ?"
		ret, err := db.Exec(sqlStr, arr[i].Reg_id)
		if err != nil {
			fmt.Println("delete failed, err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			jsonk := `{"isok":"no","msg":""}`
			fmt.Println("返回前台数据为：", jsonk)
			w.Write([]byte(jsonk)) //给客户端返回数据。
			return
		}
		fmt.Println("delete success, affected rows:%d\n", n)

		sqlStr = "update class set persons_curr=persons_curr-1 where id = ?"
		ret, err = db.Exec(sqlStr, arr[i].Class_id)
		if err != nil {
			fmt.Printf("update failed, err:%v\n", err)
			return
		}
		n, err = ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			return
		}
	}
}

// 删除报名信息， 并且数据库数量不减少
func Delete_class_ids(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")

	register_arr_str := r.PostFormValue("register_arr")
	fmt.Println("register_arr_str=", register_arr_str)
	type register_class struct {
		Reg_id   int `db:"reg_id"json:"reg_id"`
		Class_id int `db:"class_id"json:"class_id"`
		Iphone string `db:"iphone"json:"iphone"`
		Class_name string `db:"class_name"json:"class_name"`
	}
	var arr []register_class
	err := json.Unmarshal([]byte(register_arr_str), &arr)
	if err != nil {
		fmt.Println("json unmarshal failed!=", err)
		return
	}
	//fmt.Println(arr[0].Reg_id)

	for i := 0; i < len(arr); i++ {
		sqlStr := "delete from register_record where id = ?"
		ret, err := db.Exec(sqlStr, arr[i].Reg_id)
		if err != nil {
			fmt.Println("delete failed, err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected() // 操作影响的行数
		if err != nil {
			fmt.Printf("get RowsAffected failed, err:%v\n", err)
			jsonk := `{"isok":"no","msg":""}`
			fmt.Println("返回前台数据为：", jsonk)
			w.Write([]byte(jsonk)) //给客户端返回数据。
			return
		}
		fmt.Println("delete success, affected rows:%d\n", n)
	}
}
const maxUploadSize = 10 * 1024 * 1024 // 10 mb
// 上传图片服务器
func UploadFileHandler_images(w http.ResponseWriter, r *http.Request)  {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	fmt.Println("调用了文件上传")
	if r.Method == "GET" {
		jsonk := `{"isok":"no","msg":"不支持get方式提交。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	if err := r.ParseMultipartForm(maxUploadSize); err != nil {
		jsonk := `{"isok":"no","msg":"文件过大。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}

	// parse and validate file and post parameters
	// 解析并验证文件和发布参数
	file, fileHeader, err := r.FormFile("file_name")
	if err != nil {
		jsonk := `{"isok":"no","msg":"请检查文件上传名称。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	defer file.Close()

	headerByte, _ := json.Marshal(fileHeader.Header)
	fmt.Println("Filename=",fileHeader.Filename)
	fmt.Println("Size=",fileHeader.Size)
	fmt.Println("FileHeader=",string(headerByte))
	// validate file size
	fileSize := fileHeader.Size
	if fileSize > maxUploadSize {
		jsonk := `{"isok":"no","msg":"上传文件过大。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	fileBytes, err := ioutil.ReadAll(file)
	if err != nil {
		jsonk := `{"isok":"no","msg":"读取文件内容失败。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}

	// check file type, detectcontenttype only needs the first 512 bytes
	detectedFileType := http.DetectContentType(fileBytes)
	switch detectedFileType {
	case "image/jpeg", "image/jpg":
	case "image/gif", "image/png":
	case "application/pdf":
		break
	default:
		jsonk := `{"isok":"no","msg":"文件类型不符合。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	timeUnix := time.Now().Unix() //单位秒 时间戳
	fileName := strconv.FormatInt(timeUnix, 10)
	fileEndings, err := mime.ExtensionsByType(detectedFileType)
	fmt.Println("fileEndings=",fileEndings)
	if err != nil {
		jsonk := `{"isok":"no","msg":"文件获取错误。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	uploadPath := "/www/wwwroot/www.lmtqlndx.com/images"
	uploadPath2 := "/images/"
	newPath := filepath.Join(uploadPath, fileName+fileEndings[len(fileEndings)-1])
	fmt.Printf("FileType: %s, File: %s\n", detectedFileType, newPath)

	// write file
	newFile, err := os.Create(newPath)
	if err != nil {
		jsonk := `{"isok":"no","msg":"打开服务器文件失败。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	defer newFile.Close() // idempotent, okay to call twice
	if _, err := newFile.Write(fileBytes); err != nil || newFile.Close() != nil {
		jsonk := `{"isok":"no","msg":"写入服务器文件失败。"}`
		w.Write([]byte(jsonk)) //给客户端返回数据。
		return
	}
	uploadPath2=uploadPath2+fileName+fileEndings[len(fileEndings)-1]
	jsonk := `{"isok":"ok","msg":"上传文件成功。","file_path":"`+uploadPath2+`"}`
	w.Write([]byte(jsonk)) //给客户端返回数据。
	return
}

